{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                        _id      date  long_openIntr  long_openIntr_chg  \\\n",
      "0  5cf3bc7c9fbea9229c5238f9  20190510         7323.0               38.0   \n",
      "1  5cf3bc7c9fbea9229c5238fa  20190510         7275.0             -120.0   \n",
      "2  5cf3bc7c9fbea9229c5238fb  20190510         4885.0              152.0   \n",
      "3  5cf3bc7c9fbea9229c5238fc  20190510         3631.0               52.0   \n",
      "4  5cf3bc7c9fbea9229c5238fd  20190510         3227.0               39.0   \n",
      "\n",
      "  long_party_name  rank  short_openIntr  short_openIntr_chg short_party_name  \\\n",
      "0            中信期货     1          7867.0              -103.0             中信期货   \n",
      "1            华泰期货     2          6855.0               -96.0             华泰期货   \n",
      "2            光大期货     3          5003.0               217.0             光大期货   \n",
      "3            银河期货     4          3652.0              -204.0             银河期货   \n",
      "4            永安期货     5          3330.0               -84.0             国泰君安   \n",
      "\n",
      "  symbol variety      vol  vol_chg vol_party_name  \n",
      "0     AP      AP  23472.0  -8426.0           华泰期货  \n",
      "1     AP      AP  12878.0     96.0           东证期货  \n",
      "2     AP      AP  11313.0  -7184.0           徽商期货  \n",
      "3     AP      AP  11051.0  -4291.0           中信期货  \n",
      "4     AP      AP  10418.0  -3393.0           华安期货  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variety</th>\n",
       "      <th>RU</th>\n",
       "      <th>MA</th>\n",
       "      <th>V</th>\n",
       "      <th>L</th>\n",
       "      <th>PP</th>\n",
       "      <th>BU</th>\n",
       "      <th>TA</th>\n",
       "      <th>商品板块</th>\n",
       "      <th>工业品</th>\n",
       "      <th>化工板块</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>会员简称</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20190510</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-186085900</td>\n",
       "      <td>-1414249200</td>\n",
       "      <td>-527998080</td>\n",
       "      <td>-90509370</td>\n",
       "      <td>533492400</td>\n",
       "      <td>-362316240</td>\n",
       "      <td>2706278790</td>\n",
       "      <td>13267140336</td>\n",
       "      <td>4898738120</td>\n",
       "      <td>658612400</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190513</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-311240710</td>\n",
       "      <td>-1486607420</td>\n",
       "      <td>-512435890</td>\n",
       "      <td>-179961250</td>\n",
       "      <td>525641875</td>\n",
       "      <td>-369247200</td>\n",
       "      <td>2167788285</td>\n",
       "      <td>5166389851</td>\n",
       "      <td>2609351958</td>\n",
       "      <td>-166062310</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190514</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-40361040</td>\n",
       "      <td>-1726866730</td>\n",
       "      <td>-451180680</td>\n",
       "      <td>-278339550</td>\n",
       "      <td>517394485</td>\n",
       "      <td>-272170500</td>\n",
       "      <td>1891588725</td>\n",
       "      <td>-3274022810</td>\n",
       "      <td>-884531693</td>\n",
       "      <td>-359935290</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190515</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-91419000</td>\n",
       "      <td>-1651912010</td>\n",
       "      <td>-400343040</td>\n",
       "      <td>-331462400</td>\n",
       "      <td>791254620</td>\n",
       "      <td>-124138150</td>\n",
       "      <td>2135968200</td>\n",
       "      <td>1165491534</td>\n",
       "      <td>872799594</td>\n",
       "      <td>327948220</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190516</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-201357820</td>\n",
       "      <td>-1765069950</td>\n",
       "      <td>-399043125</td>\n",
       "      <td>-432622500</td>\n",
       "      <td>972595910</td>\n",
       "      <td>-48706360</td>\n",
       "      <td>2249938900</td>\n",
       "      <td>10482803129</td>\n",
       "      <td>4233754939</td>\n",
       "      <td>375735055</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190517</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-165167660</td>\n",
       "      <td>-1391379500</td>\n",
       "      <td>-307747440</td>\n",
       "      <td>-418460000</td>\n",
       "      <td>1073357940</td>\n",
       "      <td>-48203880</td>\n",
       "      <td>1931710800</td>\n",
       "      <td>9240086523</td>\n",
       "      <td>4662698756</td>\n",
       "      <td>674110260</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190520</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-189310560</td>\n",
       "      <td>-1440133830</td>\n",
       "      <td>-332881280</td>\n",
       "      <td>-492123655</td>\n",
       "      <td>1201346100</td>\n",
       "      <td>62874000</td>\n",
       "      <td>2112362055</td>\n",
       "      <td>16896947990</td>\n",
       "      <td>7270000130</td>\n",
       "      <td>922132830</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190521</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-223014400</td>\n",
       "      <td>-1415188800</td>\n",
       "      <td>-369670175</td>\n",
       "      <td>-331446930</td>\n",
       "      <td>1214363040</td>\n",
       "      <td>12301500</td>\n",
       "      <td>2015191950</td>\n",
       "      <td>10973891482</td>\n",
       "      <td>6155068116</td>\n",
       "      <td>902536185</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190522</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-200185800</td>\n",
       "      <td>-1436910090</td>\n",
       "      <td>-347091525</td>\n",
       "      <td>-324374000</td>\n",
       "      <td>1201899035</td>\n",
       "      <td>18592200</td>\n",
       "      <td>2317725540</td>\n",
       "      <td>12174628961</td>\n",
       "      <td>6115468215</td>\n",
       "      <td>1229655360</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190523</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-136077060</td>\n",
       "      <td>-1580729320</td>\n",
       "      <td>-268500330</td>\n",
       "      <td>-309272520</td>\n",
       "      <td>1185771140</td>\n",
       "      <td>177319080</td>\n",
       "      <td>2229437010</td>\n",
       "      <td>11486905934</td>\n",
       "      <td>5770167779</td>\n",
       "      <td>1297948000</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190524</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-140526200</td>\n",
       "      <td>-1503891060</td>\n",
       "      <td>-234165560</td>\n",
       "      <td>-403104000</td>\n",
       "      <td>1055184860</td>\n",
       "      <td>85979670</td>\n",
       "      <td>2220016050</td>\n",
       "      <td>10252923705</td>\n",
       "      <td>5741627025</td>\n",
       "      <td>1079493760</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190527</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-96345920</td>\n",
       "      <td>-1435288340</td>\n",
       "      <td>-260134140</td>\n",
       "      <td>-382169545</td>\n",
       "      <td>1033055650</td>\n",
       "      <td>-114844030</td>\n",
       "      <td>2297775350</td>\n",
       "      <td>8651339810</td>\n",
       "      <td>4263897115</td>\n",
       "      <td>1042049025</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190528</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-43544980</td>\n",
       "      <td>-1422465570</td>\n",
       "      <td>-321594520</td>\n",
       "      <td>-376632990</td>\n",
       "      <td>847060000</td>\n",
       "      <td>-79189920</td>\n",
       "      <td>2394329080</td>\n",
       "      <td>2904410836</td>\n",
       "      <td>2870347506</td>\n",
       "      <td>997961100</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190529</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>8228270</td>\n",
       "      <td>-1522634880</td>\n",
       "      <td>-426192920</td>\n",
       "      <td>-287669400</td>\n",
       "      <td>1209764740</td>\n",
       "      <td>-95167800</td>\n",
       "      <td>2376926400</td>\n",
       "      <td>241834975</td>\n",
       "      <td>2185715166</td>\n",
       "      <td>1263254410</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190530</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-31954560</td>\n",
       "      <td>-1868121710</td>\n",
       "      <td>-434217250</td>\n",
       "      <td>-280918880</td>\n",
       "      <td>1121486520</td>\n",
       "      <td>-80335680</td>\n",
       "      <td>2373431395</td>\n",
       "      <td>1554800750</td>\n",
       "      <td>2082693524</td>\n",
       "      <td>799369835</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190531</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>25611040</td>\n",
       "      <td>-2140974660</td>\n",
       "      <td>-281826540</td>\n",
       "      <td>-344309250</td>\n",
       "      <td>1252911450</td>\n",
       "      <td>-167896000</td>\n",
       "      <td>2184914400</td>\n",
       "      <td>-1745331372</td>\n",
       "      <td>924433655</td>\n",
       "      <td>528430440</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190603</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>35568720</td>\n",
       "      <td>-2218279390</td>\n",
       "      <td>-320522400</td>\n",
       "      <td>-416323200</td>\n",
       "      <td>1285518920</td>\n",
       "      <td>-258148820</td>\n",
       "      <td>2165146680</td>\n",
       "      <td>-4415134670</td>\n",
       "      <td>1030313720</td>\n",
       "      <td>272960510</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190604</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>27939030</td>\n",
       "      <td>-2143060100</td>\n",
       "      <td>-226220325</td>\n",
       "      <td>-450059040</td>\n",
       "      <td>1365396960</td>\n",
       "      <td>-232992310</td>\n",
       "      <td>2206645875</td>\n",
       "      <td>100314914</td>\n",
       "      <td>2719612310</td>\n",
       "      <td>547650090</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190605</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>93329440</td>\n",
       "      <td>-2037324340</td>\n",
       "      <td>-98520525</td>\n",
       "      <td>-1158820470</td>\n",
       "      <td>1233902790</td>\n",
       "      <td>-294400020</td>\n",
       "      <td>2174581765</td>\n",
       "      <td>-2327739734</td>\n",
       "      <td>1310364975</td>\n",
       "      <td>-87251360</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190606</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>157932000</td>\n",
       "      <td>-1974041800</td>\n",
       "      <td>17579940</td>\n",
       "      <td>-1054602445</td>\n",
       "      <td>1549672000</td>\n",
       "      <td>-583719940</td>\n",
       "      <td>2129601600</td>\n",
       "      <td>-2653049439</td>\n",
       "      <td>1519735107</td>\n",
       "      <td>242421355</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190610</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-116601950</td>\n",
       "      <td>-1912163960</td>\n",
       "      <td>111945000</td>\n",
       "      <td>-1026022050</td>\n",
       "      <td>1333679510</td>\n",
       "      <td>-475605520</td>\n",
       "      <td>2220498675</td>\n",
       "      <td>-1336507392</td>\n",
       "      <td>2502024426</td>\n",
       "      <td>135729705</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190611</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-75114600</td>\n",
       "      <td>-2269223050</td>\n",
       "      <td>-130201080</td>\n",
       "      <td>-849916440</td>\n",
       "      <td>884092635</td>\n",
       "      <td>-421851740</td>\n",
       "      <td>1993475250</td>\n",
       "      <td>9974925056</td>\n",
       "      <td>6405427016</td>\n",
       "      <td>-868739025</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190612</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-57527540</td>\n",
       "      <td>-2034143000</td>\n",
       "      <td>-20832480</td>\n",
       "      <td>-680828265</td>\n",
       "      <td>765793840</td>\n",
       "      <td>-501501200</td>\n",
       "      <td>1897252700</td>\n",
       "      <td>11797988664</td>\n",
       "      <td>6378548607</td>\n",
       "      <td>-631785945</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190613</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-66335610</td>\n",
       "      <td>-1619386290</td>\n",
       "      <td>28564720</td>\n",
       "      <td>-808486630</td>\n",
       "      <td>632464880</td>\n",
       "      <td>-687177600</td>\n",
       "      <td>1713798360</td>\n",
       "      <td>11866087884</td>\n",
       "      <td>6940096695</td>\n",
       "      <td>-806558170</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190614</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-98383140</td>\n",
       "      <td>-1580639220</td>\n",
       "      <td>173341960</td>\n",
       "      <td>-759026710</td>\n",
       "      <td>685866150</td>\n",
       "      <td>-563097480</td>\n",
       "      <td>1972300000</td>\n",
       "      <td>12380945498</td>\n",
       "      <td>6873057190</td>\n",
       "      <td>-169638440</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190617</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-7102800</td>\n",
       "      <td>-1402169300</td>\n",
       "      <td>151647235</td>\n",
       "      <td>-808504685</td>\n",
       "      <td>559649090</td>\n",
       "      <td>-648864780</td>\n",
       "      <td>2355893720</td>\n",
       "      <td>9068735835</td>\n",
       "      <td>4818707055</td>\n",
       "      <td>200548480</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190618</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>84626240</td>\n",
       "      <td>-1444650400</td>\n",
       "      <td>76833300</td>\n",
       "      <td>-815707195</td>\n",
       "      <td>532344820</td>\n",
       "      <td>-793563420</td>\n",
       "      <td>2707505920</td>\n",
       "      <td>13187778841</td>\n",
       "      <td>6094230817</td>\n",
       "      <td>347389265</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190619</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-32389540</td>\n",
       "      <td>-1492649700</td>\n",
       "      <td>-283602200</td>\n",
       "      <td>-1186318080</td>\n",
       "      <td>411130290</td>\n",
       "      <td>-860217020</td>\n",
       "      <td>3170679995</td>\n",
       "      <td>11348079441</td>\n",
       "      <td>5368818345</td>\n",
       "      <td>-273366255</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190620</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>-15157760</td>\n",
       "      <td>-1606816920</td>\n",
       "      <td>-386456700</td>\n",
       "      <td>-1424488750</td>\n",
       "      <td>487403760</td>\n",
       "      <td>-701637560</td>\n",
       "      <td>3506555700</td>\n",
       "      <td>13125961213</td>\n",
       "      <td>5758158995</td>\n",
       "      <td>-140598230</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190621</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>66681720</td>\n",
       "      <td>-1823213250</td>\n",
       "      <td>-427408220</td>\n",
       "      <td>-1441630890</td>\n",
       "      <td>372292250</td>\n",
       "      <td>-734824600</td>\n",
       "      <td>3338637500</td>\n",
       "      <td>3850993827</td>\n",
       "      <td>2832129536</td>\n",
       "      <td>-649465490</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20190624</th>\n",
       "      <th>永安期货</th>\n",
       "      <td>11686000</td>\n",
       "      <td>-1889788230</td>\n",
       "      <td>-684297360</td>\n",
       "      <td>-1320775215</td>\n",
       "      <td>482797000</td>\n",
       "      <td>-713837880</td>\n",
       "      <td>4008350640</td>\n",
       "      <td>10263703755</td>\n",
       "      <td>4537171380</td>\n",
       "      <td>-105865045</td>\n",
       "      <td>TA</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "variety               RU          MA          V           L          PP  \\\n",
       "date     会员简称                                                             \n",
       "20190510 永安期货 -186085900 -1414249200 -527998080   -90509370   533492400   \n",
       "20190513 永安期货 -311240710 -1486607420 -512435890  -179961250   525641875   \n",
       "20190514 永安期货  -40361040 -1726866730 -451180680  -278339550   517394485   \n",
       "20190515 永安期货  -91419000 -1651912010 -400343040  -331462400   791254620   \n",
       "20190516 永安期货 -201357820 -1765069950 -399043125  -432622500   972595910   \n",
       "20190517 永安期货 -165167660 -1391379500 -307747440  -418460000  1073357940   \n",
       "20190520 永安期货 -189310560 -1440133830 -332881280  -492123655  1201346100   \n",
       "20190521 永安期货 -223014400 -1415188800 -369670175  -331446930  1214363040   \n",
       "20190522 永安期货 -200185800 -1436910090 -347091525  -324374000  1201899035   \n",
       "20190523 永安期货 -136077060 -1580729320 -268500330  -309272520  1185771140   \n",
       "20190524 永安期货 -140526200 -1503891060 -234165560  -403104000  1055184860   \n",
       "20190527 永安期货  -96345920 -1435288340 -260134140  -382169545  1033055650   \n",
       "20190528 永安期货  -43544980 -1422465570 -321594520  -376632990   847060000   \n",
       "20190529 永安期货    8228270 -1522634880 -426192920  -287669400  1209764740   \n",
       "20190530 永安期货  -31954560 -1868121710 -434217250  -280918880  1121486520   \n",
       "20190531 永安期货   25611040 -2140974660 -281826540  -344309250  1252911450   \n",
       "20190603 永安期货   35568720 -2218279390 -320522400  -416323200  1285518920   \n",
       "20190604 永安期货   27939030 -2143060100 -226220325  -450059040  1365396960   \n",
       "20190605 永安期货   93329440 -2037324340  -98520525 -1158820470  1233902790   \n",
       "20190606 永安期货  157932000 -1974041800   17579940 -1054602445  1549672000   \n",
       "20190610 永安期货 -116601950 -1912163960  111945000 -1026022050  1333679510   \n",
       "20190611 永安期货  -75114600 -2269223050 -130201080  -849916440   884092635   \n",
       "20190612 永安期货  -57527540 -2034143000  -20832480  -680828265   765793840   \n",
       "20190613 永安期货  -66335610 -1619386290   28564720  -808486630   632464880   \n",
       "20190614 永安期货  -98383140 -1580639220  173341960  -759026710   685866150   \n",
       "20190617 永安期货   -7102800 -1402169300  151647235  -808504685   559649090   \n",
       "20190618 永安期货   84626240 -1444650400   76833300  -815707195   532344820   \n",
       "20190619 永安期货  -32389540 -1492649700 -283602200 -1186318080   411130290   \n",
       "20190620 永安期货  -15157760 -1606816920 -386456700 -1424488750   487403760   \n",
       "20190621 永安期货   66681720 -1823213250 -427408220 -1441630890   372292250   \n",
       "20190624 永安期货   11686000 -1889788230 -684297360 -1320775215   482797000   \n",
       "\n",
       "variety               BU          TA         商品板块         工业品        化工板块 max  \\\n",
       "date     会员简称                                                                   \n",
       "20190510 永安期货 -362316240  2706278790  13267140336  4898738120   658612400  TA   \n",
       "20190513 永安期货 -369247200  2167788285   5166389851  2609351958  -166062310  TA   \n",
       "20190514 永安期货 -272170500  1891588725  -3274022810  -884531693  -359935290  TA   \n",
       "20190515 永安期货 -124138150  2135968200   1165491534   872799594   327948220  TA   \n",
       "20190516 永安期货  -48706360  2249938900  10482803129  4233754939   375735055  TA   \n",
       "20190517 永安期货  -48203880  1931710800   9240086523  4662698756   674110260  TA   \n",
       "20190520 永安期货   62874000  2112362055  16896947990  7270000130   922132830  TA   \n",
       "20190521 永安期货   12301500  2015191950  10973891482  6155068116   902536185  TA   \n",
       "20190522 永安期货   18592200  2317725540  12174628961  6115468215  1229655360  TA   \n",
       "20190523 永安期货  177319080  2229437010  11486905934  5770167779  1297948000  TA   \n",
       "20190524 永安期货   85979670  2220016050  10252923705  5741627025  1079493760  TA   \n",
       "20190527 永安期货 -114844030  2297775350   8651339810  4263897115  1042049025  TA   \n",
       "20190528 永安期货  -79189920  2394329080   2904410836  2870347506   997961100  TA   \n",
       "20190529 永安期货  -95167800  2376926400    241834975  2185715166  1263254410  TA   \n",
       "20190530 永安期货  -80335680  2373431395   1554800750  2082693524   799369835  TA   \n",
       "20190531 永安期货 -167896000  2184914400  -1745331372   924433655   528430440  TA   \n",
       "20190603 永安期货 -258148820  2165146680  -4415134670  1030313720   272960510  TA   \n",
       "20190604 永安期货 -232992310  2206645875    100314914  2719612310   547650090  TA   \n",
       "20190605 永安期货 -294400020  2174581765  -2327739734  1310364975   -87251360  TA   \n",
       "20190606 永安期货 -583719940  2129601600  -2653049439  1519735107   242421355  TA   \n",
       "20190610 永安期货 -475605520  2220498675  -1336507392  2502024426   135729705  TA   \n",
       "20190611 永安期货 -421851740  1993475250   9974925056  6405427016  -868739025  TA   \n",
       "20190612 永安期货 -501501200  1897252700  11797988664  6378548607  -631785945  TA   \n",
       "20190613 永安期货 -687177600  1713798360  11866087884  6940096695  -806558170  TA   \n",
       "20190614 永安期货 -563097480  1972300000  12380945498  6873057190  -169638440  TA   \n",
       "20190617 永安期货 -648864780  2355893720   9068735835  4818707055   200548480  TA   \n",
       "20190618 永安期货 -793563420  2707505920  13187778841  6094230817   347389265  TA   \n",
       "20190619 永安期货 -860217020  3170679995  11348079441  5368818345  -273366255  TA   \n",
       "20190620 永安期货 -701637560  3506555700  13125961213  5758158995  -140598230  TA   \n",
       "20190621 永安期货 -734824600  3338637500   3850993827  2832129536  -649465490  TA   \n",
       "20190624 永安期货 -713837880  4008350640  10263703755  4537171380  -105865045  TA   \n",
       "\n",
       "variety       min  \n",
       "date     会员简称      \n",
       "20190510 永安期货  MA  \n",
       "20190513 永安期货  MA  \n",
       "20190514 永安期货  MA  \n",
       "20190515 永安期货  MA  \n",
       "20190516 永安期货  MA  \n",
       "20190517 永安期货  MA  \n",
       "20190520 永安期货  MA  \n",
       "20190521 永安期货  MA  \n",
       "20190522 永安期货  MA  \n",
       "20190523 永安期货  MA  \n",
       "20190524 永安期货  MA  \n",
       "20190527 永安期货  MA  \n",
       "20190528 永安期货  MA  \n",
       "20190529 永安期货  MA  \n",
       "20190530 永安期货  MA  \n",
       "20190531 永安期货  MA  \n",
       "20190603 永安期货  MA  \n",
       "20190604 永安期货  MA  \n",
       "20190605 永安期货  MA  \n",
       "20190606 永安期货  MA  \n",
       "20190610 永安期货  MA  \n",
       "20190611 永安期货  MA  \n",
       "20190612 永安期货  MA  \n",
       "20190613 永安期货  MA  \n",
       "20190614 永安期货  MA  \n",
       "20190617 永安期货  MA  \n",
       "20190618 永安期货  MA  \n",
       "20190619 永安期货  MA  \n",
       "20190620 永安期货  MA  \n",
       "20190621 永安期货  MA  \n",
       "20190624 永安期货  MA  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymongo\n",
    "import pandas as pd\n",
    "import matplotlib as plt\n",
    "from pandas import Series,DataFrame\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "#二行即可搞定画图中文乱码\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "#连接数据库\n",
    "client = pymongo.MongoClient('localhost',27017)\n",
    "futures = client.futures\n",
    "\n",
    "market = futures.indexMarket\n",
    "unit = futures.unit\n",
    "position = futures.position\n",
    "\n",
    "date = \"20190510\"\n",
    "\n",
    "#加载数据\n",
    "market = DataFrame(list(market.find({'date': {'$gte': date}})))\n",
    "unit = DataFrame(list(unit.find()))\n",
    "position = DataFrame(list(position.find({'date': {'$gte': date}})))\n",
    "print(position.head())\n",
    "#大写字母\n",
    "position=position.rename(columns={'var':'variety'})\n",
    "\n",
    "#删除/选取某行含有特殊数值的列\n",
    "position=position.dropna()\n",
    "#选择需要显示的字段\n",
    "\n",
    "data1=market[['date','variety','set_close']]\n",
    "\n",
    "data2=unit[[ 'variety','unit']]\n",
    "position=position[['date','variety','symbol','long_party_name','long_openIntr', 'long_openIntr_chg','short_party_name','short_openIntr', 'short_openIntr_chg']]\n",
    "\n",
    "#查询会员\n",
    "members='永安期货'\n",
    "data3=position[(position['long_party_name'] == members)]\n",
    "\n",
    "#汇总合约\n",
    "data3=data3[['date','variety','long_party_name','long_openIntr']]\n",
    "\n",
    "data3=data3.groupby(['date','variety','long_party_name'])[['long_openIntr']].sum()\n",
    "\n",
    "data4=position[(position['short_party_name'] == members)]\n",
    "data4=data4[['date','variety','short_party_name','short_openIntr']]\n",
    "data4=data4.groupby(['date','variety','short_party_name'])[['short_openIntr']].sum()\n",
    "\n",
    "\n",
    "#并集\n",
    "data5=pd.merge(data3,data4, on=['date','variety'],how='outer')\n",
    "data5['会员简称']=data5.apply(lambda x: members,axis=1)\n",
    "\n",
    "#nan缺失值填充fillna()为0\n",
    "data5=data5.fillna(0)\n",
    "data5['净持仓']=data5.apply(lambda x: x['long_openIntr']-x['short_openIntr'],axis=1)\n",
    "\n",
    "# df['当日涨幅']=df.apply(lambda x: x['收盘']-x['收盘'].shift(1)-1,axis=1)\n",
    "#选择需要显示的字段\n",
    "data5=data5[['会员简称','long_openIntr','short_openIntr','净持仓']]\n",
    "data5=data5.reset_index(['variety','date'])\n",
    "netpostion=data5.set_index('date')\n",
    "\n",
    "netpostion=data5.pivot_table('净持仓',index='date',columns='variety',fill_value=0)\n",
    "# print(netpostion)\n",
    "#合约价值\n",
    "contractValue=pd.merge(data1,data2,how='left',sort=False).drop_duplicates()\n",
    "contractValue['contractValue'] = contractValue.apply(lambda x: x['set_close']*x['unit'],axis=1)\n",
    "contractValue=contractValue[['date','variety','contractValue']]\n",
    "\n",
    "#值替换replace()\n",
    "# contractValue=contractValue.replace(['TA'],'PTA')\n",
    "sz=pd.merge(data5,contractValue,on=['date','variety'],how='left')\n",
    "\n",
    "#净持仓价值\n",
    "sz['净持仓价值']=sz.apply(lambda x: x['净持仓']*x['contractValue'],axis=1)\n",
    "sz=sz[['date','variety','会员简称','净持仓价值']]\n",
    "sz=sz.sort_values(by='净持仓价值')\n",
    "sz=sz.pivot_table('净持仓价值',index=['date','会员简称'],columns='variety',fill_value=0)\n",
    "sz=sz.copy()\n",
    "sz\n",
    "sz['化工板块']=sz[['RU','MA','V','L','PP','BU','TA']].sum(axis=1)\n",
    "sz['油脂板块']=sz[['Y','P','OI']].sum(axis=1)\n",
    "sz['有色板块']=sz[['CU','AL','ZN','NI','PB']].sum(axis=1)\n",
    "sz['黑色板块']=sz[['RB','HC','ZC','J','JM','I']].sum(axis=1)\n",
    "sz['黄金白银']=sz[['AU','AG']].sum(axis=1)\n",
    "sz['工业品']=sz[['CU','AL','ZN','NI','PB','RB','FG','RU','L','V','TA','MA','PP','JM','J','ZC','I','BU','HC','SM','SF','FU']].sum(axis=1)\n",
    "sz['农产品']=sz[['A','C','M','RM','Y','P','OI','CF','SR','JD','CS','AP']].sum(axis=1)\n",
    "sz['商品板块']=sz.sum(axis=1)\n",
    "\n",
    "\n",
    "chemical=sz.copy()\n",
    "chemical=chemical[['RU','MA','V','L','PP','BU','TA','商品板块','工业品','化工板块']]\n",
    "chemical['max']=chemical[['RU','MA','V','L','PP','BU','TA']].idxmax(axis=1)\n",
    "chemical['min']=chemical[['RU','MA','V','L','PP','BU']].idxmin(axis=1)\n",
    "chemical\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
